﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Data;

/// <summary>
/// Summary description for DBConnection
/// </summary>
public class DBConnection
{
	

        		//

          private SqlConnection conn;


    //Constructor
    public DBConnection()
    {
        this.conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["flowerseelingConnectionString"].ToString() );
    }

    //public ConnectDB(String HostName, String DatabaseName, String UId, String PWd)
    //{
    //    this.conn = new SqlConnection("Provider=SQLNCLI;Server=" + HostName + ";Database=" + DatabaseName + ";Uid=" + UId + ";PWd=" + PWd + ";");
    //}


    //get, set..
    public SqlConnection Conn
    {
        get { return conn; }
        set { conn = value; }
    }

    public DataTable executeQuery(String  sql)
    {
        SqlDataAdapter adapter = new SqlDataAdapter(sql, this.conn);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        return dt;
    }



    private SqlCommand getCommand(String procName, Dictionary<String, Object> paramList)
    {
        SqlCommand cmd = this.conn.CreateCommand();
        cmd.CommandText = procName;
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (KeyValuePair<String, Object> kvp in paramList)
        {
            cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
        }
        return cmd;
    }


    //thuc thi cau lenh procedure
    public DataTable executeSelectProcedure(String procName, Dictionary<String, Object> paramList)
    {
        DataTable dt = new DataTable();
        try
        {

            using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.getCommand(procName, paramList)))
            {
                sqlDataAdapter.Fill(dt);
            }
            return dt;
        }
        catch (Exception ex)
        {
            //MessageBox.Show(ex.Message);
           return null;
        }
       
    }

    public bool executeUpdateProcedure(String procName, Dictionary<String, Object> paramList)
    {
        try
        {
        if (this.conn.State == ConnectionState.Closed)
        {
            this.conn.Open();
        }
        SqlCommand cmd = this.getCommand(procName, paramList);
        return cmd.ExecuteNonQuery() > 0;
        }
        catch (Exception ex) { 
            //MessageBox.Show("Không thể  thực hiện.Vui lòng kiểm tra lại dữ liệu nhập", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return false;
        
        }
        finally
        {
            this.conn.Close();
        }

    }

    public bool executeInsertProcedure(String procName, Dictionary<String, Object> paramList)
    {
        return executeUpdateProcedure(procName, paramList);
    }
    public bool executeDeleteProcedure(String procName, Dictionary<String, Object> paramList)
    {
        return executeUpdateProcedure(procName, paramList);
    }
    public DataTable executeSearchProcedure(String procName, Dictionary<String, Object> paramList)
    {
        return executeSelectProcedure(procName, paramList);
    }
    public DataTable executeReportProcedure(String procName, Dictionary<String, Object> paramList)
    {
        return executeSelectProcedure(procName, paramList);
    }
    
    
	}
